import pandas as pd

# 读取数据（假设文件名为000300perf.xlsx）
df = pd.read_excel('python\\sh000905\\000905perf_left.xlsx', sheet_name='000905perf')

# 提取必要列并处理日期格式
df = df[['日期Date', '收盘Close']]
df['日期Date'] = pd.to_datetime(df['日期Date'], format='%Y%m%d')
df = df.sort_values('日期Date')  # 按日期排序

# 初始化变量
cumulative_shares = 0.0    # 累计份额
cumulative_investment = 0.0  # 累计投入
results = []  # 存储每日结果

# 逐日计算定投
for index, row in df.iterrows():
    close_price = row['收盘Close']
    daily_shares = 60 / close_price  # 当日买入份额
    cumulative_shares += daily_shares
    cumulative_investment += 60
    
    current_value = cumulative_shares * close_price  # 当前市值
    profit = current_value - cumulative_investment   # 累计收益
    
    results.append({
        '日期': row['日期Date'].strftime('%Y-%m-%d'),
        '收盘价': close_price,
        '当日投入(元)': 60,
        '当日买入份额': daily_shares,
        '累计投入(元)': cumulative_investment,
        '累计份额': cumulative_shares,
        '当前市值(元)': current_value,
        '累计收益(元)': profit,
        '收益率(%)': (profit / cumulative_investment) * 100 if cumulative_investment != 0 else 0
    })

# 生成结果表格
result_df = pd.DataFrame(results)

# 输出到Excel文件
result_df.to_excel('定投收益结果_000905perf_left.xlsx', index=False, float_format='%.4f')

# 打印最终结果
final_profit = results[-1]['累计收益(元)']
final_return = results[-1]['收益率(%)']
print(f"最终累计收益: {final_profit:.2f} 元")
print(f"总收益率: {final_return:.2f}%")